package com.css.fxfzcpxx.modules.thematicMap.repository.impl;

import com.css.fxfzcpxx.constants.YNEnum;
import com.css.fxfzcpxx.modules.categoryView.param.QueParam;
import com.css.fxfzcpxx.modules.categoryView.vo.CategoryViewVo;
import com.css.fxfzcpxx.modules.thematicMap.entity.ThematicMap;
import com.css.fxfzcpxx.modules.thematicMap.param.QueParams;
import com.css.fxfzcpxx.modules.thematicMap.repository.TheMapRepository;
import com.css.fxfzcpxx.modules.thematicMap.vo.ThematicMapVo;
import com.css.fxfzcpxx.util.PlatformObjectUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.Timestamp;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository
public class TheMapRepositoryImpl implements TheMapRepository {
    @Resource
    JdbcTemplate jdbcTemplate;

    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    private static final String TABLE_NAME = "cpxx_thematic_map";

    @Override
    public Map<String, Object> getPage(QueParams queParams, int curPage, int pageSize) {
        StringBuilder sql = new StringBuilder("select * from cpxx_thematic_map ");
        StringBuilder whereSql = new StringBuilder(" where del_flag = '"+ YNEnum.N.toString()+"'");
        StringBuilder orderSql = new StringBuilder(" order by create_time desc");
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;
        StringBuilder limitSql = new StringBuilder(" limit "+lastIndex+" offset "+ firstIndex);
        if (!PlatformObjectUtils.isEmpty(queParams.getReleaseStatus())) {
            whereSql.append(" and release_status = '"+queParams.getReleaseStatus()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getMeansName())) {
            whereSql.append(" and means_name like '%" + queParams.getMeansName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getReleaseColumn())) {
            whereSql.append(" and release_column like '%" + queParams.getReleaseColumn() + "%'");
        }

        //时间范围
        if (!PlatformObjectUtils.isEmpty(queParams.getStartTime()) & !PlatformObjectUtils.isEmpty(queParams.getEndTime())) {
            whereSql.append("and release_date between '" + queParams.getStartTime() + "' and '" + queParams.getEndTime() + "'");
        }
        //大于开始时间
        if (!PlatformObjectUtils.isEmpty(queParams.getStartTime()) & PlatformObjectUtils.isEmpty(queParams.getEndTime())) {
            whereSql.append("and release_date  >='" + queParams.getStartTime() + "'");
        }
        //小于结束时间
        if ( !PlatformObjectUtils.isEmpty(queParams.getEndTime()) & PlatformObjectUtils.isEmpty(queParams.getStartTime())) {
            whereSql.append("and release_date  <='" + queParams.getEndTime() + "'");
        }


        sql.append(whereSql).append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(ThematicMap.class)).size();
        List<ThematicMap> list = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(ThematicMap.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    @Override
    public void save(ThematicMap thematicMap) {
        String sql = "insert into "+TABLE_NAME+"(id,means_name,release_column,release_status,release_date,plotting_scale,province,city," +
                "county,picture_file,picture_describe,keyword,remark,create_user,create_time,update_user,update_time,del_flag,means_type,plot_time,drafting_unit,publishing_unit,number,flag,magnitude,type,disaster_type,notes) " +
                "values(:id, :meansName, :releaseColumn, :releaseStatus, :releaseDate, :plottingScale, :province, :city, " +
                ":county, :pictureFile, :pictureDescribe, :keyword, :remark, :createUser, :createTime, :updateUser, :updateTime, :delFlag, :meansType, :plotTime, :draftingUnit, :publishingUnit,:number,:flag,:magnitude,:type,:disasterType,:notes)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(thematicMap);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public void recall(String releaseStatus, List<String> ids) {
        String sql = "update cpxx_thematic_map set release_status = '"+releaseStatus+"' where id in  ('" + String.join("','", ids) + "')";
        jdbcTemplate.update(sql);
    }

    @Override
    public void publish(String releaseStatus, List<String> ids, Date date) {
        String sql = "update cpxx_thematic_map set release_status = '"+releaseStatus+"',release_date='"+date+"' where id in  ('" + String.join("','", ids) + "')";
        jdbcTemplate.update(sql);
    }

    @Override
    public void delete(List<String> ids) {
        String sql = "update cpxx_thematic_map set del_flag = '"+YNEnum.Y.toString()+"' where id in  ('" + String.join("','", ids) + "')";
        jdbcTemplate.update(sql);
    }

    @Override
    public ThematicMap findById(String id) {
        String sql = "select * from cpxx_thematic_map where id = ? ";
        ThematicMap thematicMap = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(ThematicMap.class), id);
        return thematicMap;
    }

    @Override
    public List<ThematicMap> getkeywordManageData(List<String> ids) {
        String sql = "select * from "+TABLE_NAME+" where id in  ('" + String.join("','", ids) + "')";
        List<ThematicMap> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ThematicMap.class));
        return list;
    }

    @Override
    public void keywordUpdate(String id, String keyword) {
        String sql = "update "+TABLE_NAME+" set keyword = ? where id = ?";
        jdbcTemplate.update(sql,keyword,id);
    }

    @Override
    public void publishedDataupdate(ThematicMap thematicMap) {
        String sql = "update "+TABLE_NAME+" set keyword= :keyword ,picture_describe = :pictureDescribe ,means_name= :meansName,drafting_unit=:draftingUnit," +
                "update_user = :updateUser ,update_time = :updateTime where id = :id";
        SqlParameterSource source = new BeanPropertySqlParameterSource(thematicMap);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public void unpublishedDataupdate(ThematicMap thematicMap) {
        String sql = "update cpxx_thematic_map set means_name= :meansName ,release_column = :releaseColumn ," +
                "plotting_scale= :plottingScale ,province = :province ,city = :city ,county = :county ," +
                "picture_file= :pictureFile ,picture_describe = :pictureDescribe ,keyword= :keyword ," +
                "remark = :remark ,update_user = :updateUser ,update_time = :updateTime ,plot_time =:plotTime ,drafting_unit=:draftingUnit,notes = :notes where id = :id";
        SqlParameterSource source = new BeanPropertySqlParameterSource(thematicMap);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public Map<String, Object> getDataList(QueParams queParams, int curPage, int pageSize) {
        StringBuilder sql = new StringBuilder("select * from cpxx_thematic_map");
        StringBuilder whereSql = new StringBuilder(" where del_flag = '"+ YNEnum.N.toString()+"' and release_status ='"+YNEnum.N.toString()+"'");
        StringBuilder orderSql = new StringBuilder(" order by release_date desc");
        int firstIndex = (curPage - 1) * pageSize;
        String limitSql = String.format(" limit %d  offset %d ", pageSize, firstIndex);
        //StringBuilder limitSql = new StringBuilder(" limit "+lastIndex+" offset "+ firstIndex);
        if (!PlatformObjectUtils.isEmpty(queParams.getMeansName())) {
            whereSql.append(" and means_name like '%" + queParams.getMeansName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getReleaseColumn())) {
            whereSql.append(" and release_column like '%" + queParams.getReleaseColumn() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getKeyword())) {
            whereSql.append(" and keyword like '%" + queParams.getKeyword() + "%'");
        }

        //时间范围
        if (!PlatformObjectUtils.isEmpty(queParams.getStartTime()) & !PlatformObjectUtils.isEmpty(queParams.getEndTime())) {
            whereSql.append("and release_date between '" + queParams.getStartTime() + "' and '" + queParams.getEndTime() + "'");
        }
        //大于开始时间
        if (!PlatformObjectUtils.isEmpty(queParams.getStartTime()) & PlatformObjectUtils.isEmpty(queParams.getEndTime())) {
            whereSql.append("and release_date  >='" + queParams.getStartTime() + "'");
        }
        //小于结束时间
        if ( !PlatformObjectUtils.isEmpty(queParams.getEndTime()) & PlatformObjectUtils.isEmpty(queParams.getStartTime())) {
            whereSql.append("and release_date  <='" + queParams.getEndTime() + "'");
        }


        sql.append(whereSql).append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(ThematicMapVo.class)).size();
        List<ThematicMapVo> list = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(ThematicMapVo.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    @Override
    public List<CategoryViewVo> getData(QueParam queParam) {
        StringBuilder sql = new StringBuilder("select id,means_name,release_column module2," +
                "release_date publishingDate,create_time,flag dataOrigin from "+TABLE_NAME+"");
        StringBuilder whereSql = new StringBuilder(" where del_flag = '"+ YNEnum.N.toString()+"' and release_status='0'");
        StringBuilder orderSql = new StringBuilder(" order by create_time desc");
        if (!PlatformObjectUtils.isEmpty(queParam.getModule2())) {
            whereSql.append(" and release_column like '%"+queParam.getModule2()+"%'");
        }
        if (!PlatformObjectUtils.isEmpty(queParam.getKeyword())) {
            whereSql.append(" and means_name like '%" + queParam.getKeyword() + "%'");
        }


        //时间范围
        if (!PlatformObjectUtils.isEmpty(queParam.getStartTime()) & !PlatformObjectUtils.isEmpty(queParam.getEndTime())) {
            whereSql.append("and release_date between '" + queParam.getStartTime() + "' and '" + queParam.getEndTime() + "'");
        }
        //大于开始时间
        if (!PlatformObjectUtils.isEmpty(queParam.getStartTime()) & PlatformObjectUtils.isEmpty(queParam.getEndTime())) {
            whereSql.append("and release_date  >='" + queParam.getStartTime() + "'");
        }
        //小于结束时间
        if ( !PlatformObjectUtils.isEmpty(queParam.getEndTime()) & PlatformObjectUtils.isEmpty(queParam.getStartTime())) {
            whereSql.append("and release_date  <='" + queParam.getEndTime() + "'");
        }
        List<CategoryViewVo> list = jdbcTemplate.query(sql.append(whereSql).append(orderSql).toString(), new BeanPropertyRowMapper<>(CategoryViewVo.class));

        return list;
    }

    @Override
    public String getNumber(Timestamp startTimestamp, Timestamp endTimestamp) {
        String sql = "select count(*) from "+TABLE_NAME+"  where create_time between '" + startTimestamp + "' and '" + endTimestamp + "'";
        String number = jdbcTemplate.queryForObject(sql, String.class);
        return number;

    }

    @Override
    public Integer getDownload(String unit) {
        String sql = "select sum(b.download_count) from cpxx_thematic_map a  " +
                "inner join  sys_attachment_record b on a.picture_file=b.attachment_id " +
                "where a.del_flag='0' and EXTRACT("+unit+" FROM b.download_time) = EXTRACT("+unit+" FROM CURRENT_DATE) ";
        Integer total = jdbcTemplate.queryForObject(sql, Integer.class);
        if(PlatformObjectUtils.isEmpty(total)){
            return 0;
        }
        return total;
    }
}
